﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Entities;

namespace DataAccess
{
	public class MovieDAL
	{
		private string connectionString = "Data Source=" + new System.IO.DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DoubleFeature").FullName + "\\Resources\\movies.sqlite";

		public bool Save(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO movies (movie_title, movie_director, movie_year, movie_tagline, movie_poster_path, movie_path, movie_rating, movie_type, movie_duration, movie_dimension) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Title;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Director;
				SQLiteParameter param3 = new SQLiteParameter();
				param3.Value = entity.Year;
				SQLiteParameter param4 = new SQLiteParameter();
				param4.Value = entity.TagLine;
				SQLiteParameter param5 = new SQLiteParameter();
				param5.Value = entity.Poster;
				SQLiteParameter param6 = new SQLiteParameter();
				param6.Value = entity.VideoInfo.FileName;
				SQLiteParameter param7 = new SQLiteParameter();
				param7.Value = entity.Rating;
				SQLiteParameter param8 = new SQLiteParameter();
				param8.Value = entity.VideoInfo.Type;
				SQLiteParameter param9 = new SQLiteParameter();
				param9.Value = entity.VideoInfo.Duration;
				SQLiteParameter param10 = new SQLiteParameter();
				param10.Value = entity.VideoInfo.Width + "x" + entity.VideoInfo.Height;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);
				cmd.Parameters.Add(param3);
				cmd.Parameters.Add(param4);
				cmd.Parameters.Add(param5);
				cmd.Parameters.Add(param6);
				cmd.Parameters.Add(param7);
				cmd.Parameters.Add(param8);
				cmd.Parameters.Add(param9);
				cmd.Parameters.Add(param10);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Update(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_title=?, movie_director=?, movie_year=?, movie_tagline=?, movie_rating=?, movie_path=?, movie_duration=?, movie_type=?, movie_dimension=?, movie_comments=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Title;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Director;
				SQLiteParameter param3 = new SQLiteParameter();
				param3.Value = entity.Year;
				SQLiteParameter param4 = new SQLiteParameter();
				param4.Value = entity.TagLine;
				SQLiteParameter param5 = new SQLiteParameter();
				param5.Value = entity.Rating;
				SQLiteParameter param6 = new SQLiteParameter();
				param6.Value = entity.VideoInfo.FileName;
				SQLiteParameter param7 = new SQLiteParameter();
				param7.Value = entity.VideoInfo.Duration;
				SQLiteParameter param8 = new SQLiteParameter();
				param8.Value = entity.VideoInfo.Type;
				SQLiteParameter param9 = new SQLiteParameter();
				param9.Value = entity.VideoInfo.Width + "x" + entity.VideoInfo.Height;
				SQLiteParameter param10 = new SQLiteParameter();
				param10.Value = entity.Comments;
				SQLiteParameter param11 = new SQLiteParameter();
				param11.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);
				cmd.Parameters.Add(param3);
				cmd.Parameters.Add(param4);
				cmd.Parameters.Add(param5);
				cmd.Parameters.Add(param6);
				cmd.Parameters.Add(param7);
				cmd.Parameters.Add(param8);
				cmd.Parameters.Add(param9);
				cmd.Parameters.Add(param10);
				cmd.Parameters.Add(param11);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdateViews(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_views=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Views;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdateRating(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_rating=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Rating;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdatePosterPath(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_poster_path=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Poster;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdatePath(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_path=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.VideoInfo.FileName;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public void RenameTitle(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_title=? WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Title;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();
			}
			catch (Exception e)
			{
				//log
			}
		}

		public bool Delete(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM movies WHERE movie_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Id;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

				cmd.CommandText = "DELETE FROM relationships WHERE movie_id="+entity.Id;

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdateGenres(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM relationships WHERE movie_id=? AND relationship_type='genre'", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = entity.Id;

				cmd.Parameters.Add(paramMovieIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				foreach (Genre g in entity.Genres)
					AddGenre(entity, g);

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool UpdateTags(Movie entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM relationships WHERE movie_id=? AND relationship_type='tag'", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = entity.Id;

				cmd.Parameters.Add(paramMovieIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				foreach (Tag t in entity.Tags)
					AddTag(entity, t);

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool AddGenre(Movie entity, Genre genre)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO relationships (movie_id, relationship_element, relationship_type) VALUES (?, ?, 'genre')", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = entity.Id;
				SQLiteParameter paramGenreIndex = new SQLiteParameter();
				paramGenreIndex.Value = genre.Id;

				cmd.Parameters.Add(paramMovieIndex);
				cmd.Parameters.Add(paramGenreIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool AddTag(Movie entity, Tag tag)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO relationships (movie_id, relationship_element, relationship_type) VALUES (?, ?, 'tag')", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = entity.Id;
				SQLiteParameter paramTagIndex = new SQLiteParameter();
				paramTagIndex.Value = tag.Id;

				cmd.Parameters.Add(paramMovieIndex);
				cmd.Parameters.Add(paramTagIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public List<Movie> Search(string title)
		{
			return List("SELECT * FROM movies WHERE movie_title LIKE '%"+title+"%' ORDER BY movie_title ASC");
		}

		public List<Movie> List(string SQL)
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand(SQL, conn);

			conn.Open();

			List<Movie> collection = new List<Movie>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				string[] dimension = dr["movie_dimension"].ToString().Split('x');

				Movie obj = new Movie();

				obj.Id = Convert.ToInt32(dr["movie_id"]);
				obj.Title = dr["movie_title"].ToString();
				obj.Director = dr["movie_director"].ToString();
				obj.TagLine = dr["movie_tagline"].ToString();
				obj.Year = Convert.ToInt32(dr["movie_year"]);
				obj.Rating = Convert.ToInt32(dr["movie_rating"]);
				obj.Poster = dr["movie_poster_path"].ToString();
				obj.Comments = dr["movie_comments"].ToString();

				obj.Genres = GetGenres(obj);
				obj.Tags = GetTags(obj);

				obj.VideoInfo.FileName = dr["movie_path"].ToString();
				obj.VideoInfo.Type = dr["movie_type"].ToString();
				obj.VideoInfo.Duration = dr["movie_duration"].ToString();
				obj.VideoInfo.Width = Convert.ToInt32(dimension[0]);
				obj.VideoInfo.Height = Convert.ToInt32(dimension[1]);

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}

		public List<Genre> GetGenres(Movie entity)
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM genres INNER JOIN relationships ON (genres.genre_id=relationships.relationship_element) WHERE relationships.relationship_type='genre' AND relationships.movie_id=? GROUP BY genres.genre_id", conn);

			SQLiteParameter param1 = new SQLiteParameter();
			param1.Value = entity.Id;

			cmd.Parameters.Add(param1);

			conn.Open();

			List<Genre> collection = new List<Genre>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				Genre obj = new Genre();

				obj.Id = Convert.ToInt32(dr["genre_id"]);
				obj.Name = dr["genre_name"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}

		public List<Tag> GetTags(Movie entity)
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tags INNER JOIN relationships ON (tags.tag_id=relationships.relationship_element) WHERE relationships.relationship_type='tag' AND relationships.movie_id=? GROUP BY tags.tag_id", conn);

			SQLiteParameter param1 = new SQLiteParameter();
			param1.Value = entity.Id;

			cmd.Parameters.Add(param1);

			conn.Open();

			List<Tag> collection = new List<Tag>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				Tag obj = new Tag();

				obj.Id = Convert.ToInt32(dr["tag_id"]);
				obj.Name = dr["tag_name"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}

        public List<MovieList> GetListsByMovie(Movie entity, bool isin)
        {
            string SQL;
            if(isin)
                SQL = "SELECT * FROM lists INNER JOIN relationships ON (lists.list_id=relationships.relationship_element) WHERE relationships.relationship_type='list' AND movie_id=? GROUP BY lists.list_id";
            else
                SQL = "SELECT * FROM lists WHERE lists.list_id NOT IN (SELECT lists.list_id FROM lists INNER JOIN relationships ON (lists.list_id=relationships.relationship_element) WHERE relationships.relationship_type='list' AND movie_id=?) GROUP BY lists.list_id";

            SQLiteConnection conn = new SQLiteConnection(connectionString);

            SQLiteCommand cmd = new SQLiteCommand(SQL, conn);

            SQLiteParameter param1 = new SQLiteParameter();
            param1.Value = entity.Id;

            cmd.Parameters.Add(param1);

            conn.Open();

            List<MovieList> collection = new List<MovieList>();

            SQLiteDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                MovieList obj = new MovieList();
                obj.Id = Convert.ToInt32(dr["list_id"]);
                obj.Name = dr["list_name"].ToString();
                obj.Movies = new List<Movie>();

                collection.Add(obj);
            }

            return collection;
        }

		public List<Movie> ListMoviesOldWay()
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM movies", conn);

			conn.Open();

			List<Movie> collection = new List<Movie>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				string[] dimension = dr["movie_dimension"].ToString().Split('x');

				Movie obj = new Movie();

				obj.Id = Convert.ToInt32(dr["movie_id"]);
				obj.Title = dr["movie_title"].ToString();
				/*
				obj.Director = dr["movie_director"].ToString();
				obj.TagLine = dr["movie_tagline"].ToString();
				obj.Year = Convert.ToInt32(dr["movie_year"]);
				obj.Rating = Convert.ToInt32(dr["movie_rating"]);
				obj.Poster = dr["movie_poster_path"].ToString();*/

				string[] genres = dr["movie_genre"].ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);

				for (int i = 0; i < genres.Length; i++)
				{
					Genre g = new Genre();
					g.Name = genres[i];
					obj.Genres.Add(g);
				}

				//obj.Genres = GetGenres(obj);

				/*obj.VideoInfo.FileName = dr["movie_path"].ToString();
				obj.VideoInfo.Type = dr["movie_type"].ToString();
				obj.VideoInfo.Duration = dr["movie_duration"].ToString();
				obj.VideoInfo.Width = Convert.ToInt32(dimension[0]);
				obj.VideoInfo.Height = Convert.ToInt32(dimension[1]);*/

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}

		public bool DeleteOldGenres()
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE movies SET movie_genre=''", conn);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public int GetLastSaved()
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("SELECT movie_id FROM movies ORDER BY movie_id DESC LIMIT 1", conn);

				conn.Open();

				SQLiteDataReader dr = cmd.ExecuteReader();

				dr.Read();

				/*while (dr.Read())
				{
					string[] dimension = dr["movie_dimension"].ToString().Split('x');

					Movie obj = new Movie();

					obj.Id = Convert.ToInt32(dr["movie_id"]);
					obj.Title = dr["movie_title"].ToString();
					obj.Director = dr["movie_director"].ToString();
					obj.TagLine = dr["movie_tagline"].ToString();
					obj.Year = Convert.ToInt32(dr["movie_year"]);
					obj.Rating = Convert.ToInt32(dr["movie_rating"]);
					obj.Poster = dr["movie_poster_path"].ToString();

					obj.Genres = GetGenres(obj);

					obj.VideoInfo.FileName = dr["movie_path"].ToString();
					obj.VideoInfo.Type = dr["movie_type"].ToString();
					obj.VideoInfo.Duration = dr["movie_duration"].ToString();
					obj.VideoInfo.Width = Convert.ToInt32(dimension[0]);
					obj.VideoInfo.Height = Convert.ToInt32(dimension[1]);

					collection.Add(obj);
				}*/

				conn.Close();

				return Convert.ToInt32(dr["movie_id"]);
			}
			catch (Exception e)
			{
				//log
				return 0;
			}
		}
	}
}